﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Text;

namespace Sustainalytics.GCCS.DataAccessLayer
{
    public class GCCSDbContext : IGCCSDbContext
    {
        private readonly SqlDatabase _database;
        private readonly int _commandTimeout;

        public GCCSDbContext()
        {
            _commandTimeout = 60 * 60;
        }

        public GCCSDbContext(string connectionString, int commandTimeout)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new Exception("Connection string must be provided!");

            _database = new SqlDatabase(connectionString);
            _commandTimeout = commandTimeout;
        }

        public IDataReader CurrentQuarter()
        {
            var query = new StringBuilder();
            query.AppendLine("SELECT Date, IsClosed, IsPublished, PublishedDateTime ");
            query.AppendLine("FROM dbo.GccsCurrentQuarter");

            return _database.ExecuteReader(new SqlCommand
            {
                CommandText = query.ToString(),
                CommandType = CommandType.Text,
                CommandTimeout = _commandTimeout
            });
        }

        public IDataReader OverallCompaniesSummary()
        {
            var query = new StringBuilder();
            query.AppendLine("SELECT CompanyId, GccsSignatorySince, OverallCompanyAssessment, OverallCompanyAssessmentEffectiveSince, LastUpdated, ReasonForNonCompliance");
            query.AppendLine("FROM dbo.GccsCompanyProfiles");
            query.AppendLine("WHERE GccsWorkflowStatus = 3"); // 3 = Final GC Workflow status

            return _database.ExecuteReader(new SqlCommand
            {
                CommandText = query.ToString(),
                CommandType = CommandType.Text,
                CommandTimeout = _commandTimeout
            });
        }

        public IDataReader PrinciplesSummary()
        {
            return _database.ExecuteReader(new SqlCommand
            {
                CommandText = "spGetPrinciplesSummary",
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = _commandTimeout
            });
        }

        public IDataReader PrincipleEventIndicators()
        {
            return _database.ExecuteReader(new SqlCommand
            {
                CommandText = "spGetPrincipleEventIndicators",
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = _commandTimeout
            });
        }

        public IDataReader Sources()
        {
            return _database.ExecuteReader(new SqlCommand
            {
                CommandText = "spGetSources",
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = _commandTimeout
            });
        }
    }
}
